Structured Query Language (SQL) is a standardized computer language that uses declarative statements to query, alter and define relational databases. Transact-SQL (TSQL) is a Turing-complete extension to SQL that supports procedural programming and local variables. TSQL also provides support functions for string processing, date processing, mathematics, and so on.
A stored procedure is a subroutine that accesses a relational database system. The stored procedure can be called by an application. Typically the stored procedure is stored in the database data dictionary. Stored procedures are typically used for tasks including but not limited to data validation and access control mechanisms, Stored procedures can also be used to consolidate and centralize logic that is used by multiple applications. One stored procedure can call another stored procedure.
Stored procedures may return result sets, (e.g., a particular stored procedure could return the results of a SELECT statement). Stored procedures may also include declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored procedures can receive variables, return results or modify variables and return them,
A user-defined function (UDF) can extend the functionality of a database server by adding a function that can be evaluated in SQL statements. SQL distinguishes between scalar functions, table functions and aggregate functions. A scalar function returns only a single value (or NULL), whereas a table function returns a (relational) table comprising zero or more rows, each now with one or more columns. An aggregate function operates on a collection of data and returns a single result (e.g., AVG is an aggregate function that returns the average of a group of numbers). Once created, a user-defined function may be used in expressions in SQL statements.